package com.xlh.util;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Table;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 统计excel
 * <p>
 * Created by lx on 2020/10/9.
 */
public class StatisticExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(StatisticExcelUtil.class);

    public static void outputStreamForStudentLearn(List<String> sheetNames, List<List<String>> basicData,
                                                   List<List<List<String>>> listData, String fileName,
                                                   HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createForStudentLearn(sheetNames, basicData, listData).write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    private static HSSFWorkbook createForStudentLearn(List<String> sheetNames, List<List<String>> basicData,
                                                      List<List<List<String>>> listData) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFPalette palette = workbook.getCustomPalette();

        // 设置主标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        setCellBorder(titleStyle);
        titleStyle.setFont(setFont(workbook, (short) 12, true, IndexedColors.WHITE));
        titleStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        palette.setColorAtIndex(HSSFColor.BLUE_GREY.index, (byte) 54, (byte) 96, (byte) 146);

        // 设置副标题样式
        CellStyle titleStyle2 = workbook.createCellStyle();
        setCellBorder(titleStyle2);
        titleStyle2.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        titleStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle2.setFont(setFont(workbook, null, false, null));
        palette.setColorAtIndex(HSSFColor.SKY_BLUE.index, (byte) 197, (byte) 217, (byte) 241);

        // 设置内容的样式
        CellStyle contentStyle = workbook.createCellStyle();
        setCellBorder(contentStyle);
        contentStyle.setFont(setFont(workbook, null, false, null));

        // 遍历每个单元格的内容
        for (int i = 0; i < sheetNames.size(); i++) {
            HSSFSheet sheet = workbook.createSheet(sheetNames.get(i));

            // 设置列宽
            sheet.setColumnWidth(0, 256 * 12);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 12);
            sheet.setColumnWidth(3, 256 * 12);
            sheet.setColumnWidth(4, 256 * 12);
            sheet.setColumnWidth(5, 256 * 12);

            // 第一行
            setRowContent(sheet,
                    Lists.newArrayList(titleStyle, titleStyle, titleStyle, titleStyle, titleStyle, titleStyle),
                    (short) 600, 0, Lists.newArrayList(String.format("班级：%s    课程：%s   %s统计表",
                            basicData.get(i).get(0) == null || Objects.equals(basicData.get(i).get(0), "null") ?
                                    "" : basicData.get(i).get(0),
                            basicData.get(i).get(1) == null || Objects.equals(basicData.get(i).get(1), "null") ?
                                    "" : basicData.get(i).get(1),
                            basicData.get(i).get(2) == null || Objects.equals(basicData.get(i).get(2), "null") ?
                                    "" : basicData.get(i).get(2)), "", "", "", "", ""));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

            // 第二行到第四行
            setRowContent(sheet,
                    Lists.newArrayList(titleStyle2, contentStyle, titleStyle2,
                            contentStyle, contentStyle, contentStyle),
                    null, 1, Lists.newArrayList("学生姓名：",
                            basicData.get(i).get(3) == null || Objects.equals(basicData.get(i).get(3), "null") ?
                                    "" : basicData.get(i).get(3),
                            "学生学号：",
                            basicData.get(i).get(4) == null || Objects.equals(basicData.get(i).get(4), "null") ?
                                    "" : basicData.get(i).get(4), "" , ""));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 5));
            setRowContent(sheet,
                    Lists.newArrayList(contentStyle, contentStyle, contentStyle,
                            contentStyle, contentStyle, contentStyle),
                    null, 2, Lists.newArrayList("", "", "", "", "" , ""));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 5));
            setRowContent(sheet,
                    Lists.newArrayList(titleStyle2, contentStyle, titleStyle2,
                            contentStyle, contentStyle, contentStyle),
                    null, 3, Lists.newArrayList("提交次数：",
                            basicData.get(i).get(5) == null || Objects.equals(basicData.get(i).get(5), "null") ?
                                    "" : basicData.get(i).get(5),
                            "平均分值：",
                            basicData.get(i).get(6) == null || Objects.equals(basicData.get(i).get(6), "null") ?
                                    "" : basicData.get(i).get(6), "" , ""));
            sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 5));

            // 第五行到第六行
            sheet.createRow(4);
            sheet.createRow(5);

            // 第七行 列表至数据行
            setCellListData(sheet, titleStyle2, 6, Lists.newArrayList("序号",
                    basicData.get(i).get(7), "章节公开", "状态", "得分"));
            if (CollectionUtils.isEmpty(listData)) continue;
            for (int index = 0; index < listData.get(i).size(); index++) {
                setCellListData(sheet, contentStyle, 7 + index, listData.get(i).get(index));
            }
        }

        return workbook;

    }

    public static void outputStreamForStudent(List<String> sheetNames, List<List<String>> basicData,
                                              List<Table<Integer, Integer, List<Object>>> titleData,
                                              List<List<List<String>>> listData, String fileName,
                                              HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createForStudent(sheetNames, basicData, titleData, listData).write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    private static HSSFWorkbook createForStudent(List<String> sheetNames, List<List<String>> basicData,
                                                 List<Table<Integer, Integer, List<Object>>> titleData,
                                                 List<List<List<String>>> listData) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFPalette palette = workbook.getCustomPalette();

        // 设置主标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        setCellBorder(titleStyle);
        titleStyle.setFont(setFont(workbook, (short) 12, true, IndexedColors.WHITE));
        titleStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        palette.setColorAtIndex(HSSFColor.BLUE_GREY.index, (byte) 54, (byte) 96, (byte) 146);

        // 设置副标题样式1
        CellStyle titleStyle2 = workbook.createCellStyle();
        setCellBorder(titleStyle2);
        titleStyle2.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        titleStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle2.setFont(setFont(workbook, null, false, null));
        palette.setColorAtIndex(HSSFColor.SKY_BLUE.index, (byte) 197, (byte) 217, (byte) 241);

        // 设置副标题样式2
        CellStyle titleStyle3 = workbook.createCellStyle();
        setCellBorder(titleStyle3);
        titleStyle3.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        titleStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle3.setFont(setFont(workbook, null, false, IndexedColors.WHITE));
        palette.setColorAtIndex(HSSFColor.LIGHT_BLUE.index, (byte) 83, (byte) 141, (byte) 213);

        // 设置副标题样式3
        CellStyle titleStyle4 = workbook.createCellStyle();
        setCellBorder(titleStyle4);
        titleStyle4.setAlignment(HorizontalAlignment.CENTER);
        titleStyle4.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        titleStyle4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle4.setFont(setFont(workbook, null, false, null));
        palette.setColorAtIndex(HSSFColor.SKY_BLUE.index, (byte) 197, (byte) 217, (byte) 241);

        // 设置内容的样式
        CellStyle contentStyle = workbook.createCellStyle();
        setCellBorder(contentStyle);
        contentStyle.setFont(setFont(workbook, null, false, null));

        // 遍历每个单元格的内容
        for (int i = 0; i < sheetNames.size(); i++) {
            HSSFSheet sheet = workbook.createSheet(sheetNames.get(i));

            int examNum = listData.get(i).get(0).size() - 22;

            // 设置列宽
            sheet.setColumnWidth(0, 256 * 12);
            sheet.setColumnWidth(1, 256 * 20);
            sheet.setColumnWidth(2, 256 * 17);
            sheet.setColumnWidth(3, 256 * 18);
            sheet.setColumnWidth(4, 256 * 18);
            sheet.setColumnWidth(5, 256 * 12);
            sheet.setColumnWidth(6, 256 * 12);
            sheet.setColumnWidth(7, 256 * 12);
            sheet.setColumnWidth(8, 256 * 16);
            sheet.setColumnWidth(9, 256 * 16);
            sheet.setColumnWidth(10, 256 * 13);
            sheet.setColumnWidth(11, 256 * 13);
            sheet.setColumnWidth(12, 256 * 25);
            sheet.setColumnWidth(13, 256 * 11);
            sheet.setColumnWidth(14, 256 * 10);
            sheet.setColumnWidth(15, 256 * 10);
            sheet.setColumnWidth(16, 256 * 25);
            sheet.setColumnWidth(17, 256 * 13);
            sheet.setColumnWidth(18, 256 * 12);
            sheet.setColumnWidth(19, 256 * 11);
            sheet.setColumnWidth(20, 256 * 11);
            sheet.setColumnWidth(21, 256 * 10);
            if (examNum > 0) {
                for (int j = 0; j < examNum; j++) {
                    sheet.setColumnWidth(22 + j, 256 * 10);
                }
            }

            // 第一行
            setRowContent(sheet, Lists.newArrayList(titleStyle, titleStyle, titleStyle, titleStyle,
                    titleStyle, titleStyle, titleStyle, titleStyle, titleStyle), (short) 600, 0,
                    Lists.newArrayList(String.format("班级：%s    学生学习情况详情",
                            basicData.get(i).get(0) == null || Objects.equals(basicData.get(i).get(0), "null") ?
                                    "" : basicData.get(i).get(0)),
                            "", "", "", "", "", "", "", ""));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));

            // 第二行到第五行
            setRowContent(sheet, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                    contentStyle, contentStyle, contentStyle, contentStyle, contentStyle),
                    null, 1, Lists.newArrayList("基本信息：", "学生姓名：",
                            basicData.get(i).get(1) == null || Objects.equals(basicData.get(i).get(1), "null") ?
                                    "" : basicData.get(i).get(1), "学号：",
                            basicData.get(i).get(2) == null || Objects.equals(basicData.get(i).get(2), "null") ?
                                    "" : basicData.get(i).get(2), "", "", "", ""));
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 8));
            sheet.createRow(2);
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 8));
            sheet.getRow(2).setHeight((short) 320);
            setRowContent(sheet, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                    contentStyle, titleStyle2, contentStyle, titleStyle2, contentStyle),
                    null, 3, Lists.newArrayList("活跃情况：",
                            "学习课程数：",
                            basicData.get(i).get(3) == null || Objects.equals(basicData.get(i).get(3), "null") ?
                                    "" : basicData.get(i).get(3), "注册时间：",
                            basicData.get(i).get(4) == null || Objects.equals(basicData.get(i).get(4), "null") ?
                                    "" : basicData.get(i).get(4),
                            "登录次数：",
                            basicData.get(i).get(5) == null || Objects.equals(basicData.get(i).get(5), "null") ?
                                    "" : basicData.get(i).get(5), "在线时长：",
                            basicData.get(i).get(6) == null || Objects.equals(basicData.get(i).get(6), "null") ?
                                    "" : basicData.get(i).get(6)));
            sheet.createRow(4);
            sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 8));
            sheet.getRow(4).setHeight((short) 320);

            // 设置边框
            setBorder(sheet, 0, 4, 0, 8);

            // 第六行到第七行
            sheet.createRow(5);
            sheet.createRow(6);

            // 第八行 table列表至数据行
            setTableData(sheet, titleStyle2, titleData.get(i), 7, titleStyle4);
            for (int index = 0; index < listData.get(i).size(); index++) {
                List<String> data = listData.get(i).get(index);
                List<CellStyle> styles = Lists.newArrayList();
                for (int y = 0; y < data.size(); y++) {
                    styles.add(contentStyle);
                }
                setRowContent(sheet, styles, (short) 300, 9 + index, data);
            }

        }

        return workbook;

    }

    public static void outputStreamForClassCourse(List<String> basicData,
                                                  Table<Integer, Integer, List<Object>> titleData,
                                                  List<List<String>> listData, String fileName,
                                                  HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createForClassCourse(basicData, titleData, listData).write(os);
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    private static HSSFWorkbook createForClassCourse(List<String> basicData,
                                                     Table<Integer, Integer, List<Object>> titleData,
                                                     List<List<String>> listData) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFPalette palette = workbook.getCustomPalette();

        // 设置主标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        setCellBorder(titleStyle);
        titleStyle.setFont(setFont(workbook, (short) 12, true, IndexedColors.WHITE));
        titleStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        palette.setColorAtIndex(HSSFColor.BLUE_GREY.index, (byte) 54, (byte) 96, (byte) 146);

        // 设置副标题样式1
        CellStyle titleStyle2 = workbook.createCellStyle();
        setCellBorder(titleStyle2);
        titleStyle2.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        titleStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle2.setFont(setFont(workbook, null, false, null));
        palette.setColorAtIndex(HSSFColor.SKY_BLUE.index, (byte) 197, (byte) 217, (byte) 241);

        // 设置副标题样式2
        CellStyle titleStyle3 = workbook.createCellStyle();
        setCellBorder(titleStyle3);
        titleStyle3.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        titleStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle3.setFont(setFont(workbook, null, false, IndexedColors.WHITE));
        palette.setColorAtIndex(HSSFColor.LIGHT_BLUE.index, (byte) 83, (byte) 141, (byte) 213);

        // 设置副标题样式3
        CellStyle titleStyle4 = workbook.createCellStyle();
        setCellBorder(titleStyle4);
        titleStyle4.setAlignment(HorizontalAlignment.CENTER);
        titleStyle4.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        titleStyle4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle4.setFont(setFont(workbook, null, false, null));
        palette.setColorAtIndex(HSSFColor.SKY_BLUE.index, (byte) 197, (byte) 217, (byte) 241);

        // 设置内容的样式
        CellStyle contentStyle = workbook.createCellStyle();
        setCellBorder(contentStyle);
        contentStyle.setFont(setFont(workbook, null, false, null));

        HSSFSheet sheet1 = workbook.createSheet("总体统计");
        // 设置列宽
        sheet1.setColumnWidth(0, 256 * 18);
        sheet1.setColumnWidth(1, 256 * 23);
        sheet1.setColumnWidth(2, 256 * 15);
        sheet1.setColumnWidth(3, 256 * 30);
        sheet1.setColumnWidth(4, 256 * 25);
        sheet1.setColumnWidth(5, 256 * 25);
        sheet1.setColumnWidth(6, 256 * 15);
        sheet1.setColumnWidth(7, 256 * 27);
        sheet1.setColumnWidth(8, 256 * 27);

        // 第1行
        setRowContent(sheet1, Lists.newArrayList(titleStyle, titleStyle, titleStyle, titleStyle,
                titleStyle, titleStyle, titleStyle, titleStyle, titleStyle), (short) 600, 0,
                Lists.newArrayList(String.format("课程：%s    班级：%s    总体学习情况统计",
                        basicData.get(0) == null || Objects.equals(basicData.get(0), "null") ?
                                "" : basicData.get(0),
                        basicData.get(1) == null || Objects.equals(basicData.get(1), "null") ?
                                "" : basicData.get(1)),
                        "", "", "", "", "", "", "", ""));
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));

        // 第2行
        setRowContent(sheet1, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                contentStyle, titleStyle2, contentStyle, contentStyle, contentStyle),
                null, 1, Lists.newArrayList("基本信息：", "主讲教师姓名：",
                        basicData.get(2) == null || Objects.equals(basicData.get(2), "null") ?
                                "" : basicData.get(2), "班级学生数量：",
                        basicData.get(3) == null || Objects.equals(basicData.get(3), "null") ?
                                "" : basicData.get(3), "助理教师数量：",
                        basicData.get(4) == null || Objects.equals(basicData.get(4), "null") ?
                                "" : basicData.get(4), "", ""));
        sheet1.addMergedRegion(new CellRangeAddress(1, 1, 6, 8));

        // 第3行
        sheet1.createRow(2);
        sheet1.addMergedRegion(new CellRangeAddress(2, 2, 0, 8));

        // 第4行
        setRowContent(sheet1, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                contentStyle, contentStyle, contentStyle, contentStyle, contentStyle),
                null, 3, Lists.newArrayList("考勤情况：", "出勤率：",
                        basicData.get(5) == null || Objects.equals(basicData.get(5), "null") ?
                                "" : basicData.get(5), "缺勤总数：",
                        basicData.get(6) == null || Objects.equals(basicData.get(6), "null") ?
                                "" : basicData.get(6), "", "", "", ""));
        sheet1.addMergedRegion(new CellRangeAddress(3, 3, 4, 8));

        // 第5行
        sheet1.createRow(4);
        sheet1.addMergedRegion(new CellRangeAddress(4, 4, 0, 8));

        // 第6行
        setRowContent(sheet1, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                contentStyle, contentStyle, contentStyle, contentStyle, contentStyle),
                null, 5, Lists.newArrayList("考试情况：", "班级考试次数：",
                        basicData.get(7) == null || Objects.equals(basicData.get(7), "null") ?
                                "" : basicData.get(7), "每次考试平均成绩：",
                        basicData.get(8) == null || Objects.equals(basicData.get(8), "null") ?
                                "" : basicData.get(8), "", "", "", ""));
        sheet1.addMergedRegion(new CellRangeAddress(5, 5, 4, 8));

        // 第7行
        sheet1.createRow(6);
        sheet1.addMergedRegion(new CellRangeAddress(6, 6, 0, 8));

        // 第8行
        setRowContent(sheet1, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                contentStyle, titleStyle2, contentStyle, titleStyle2, contentStyle),
                null, 7, Lists.newArrayList("实验课：", "实验视频总时长：",
                        basicData.get(9) == null || Objects.equals(basicData.get(9), "null") ?
                                "" : basicData.get(9), "实验学习总时长：",
                        basicData.get(10) == null || Objects.equals(basicData.get(10), "null") ?
                                "" : basicData.get(10), "实验通过率：",
                        basicData.get(11) == null || Objects.equals(basicData.get(11), "null") ?
                                "" : basicData.get(11),
                        "实验报告提交率：",
                        basicData.get(12) == null || Objects.equals(basicData.get(12), "null") ?
                                "" : basicData.get(12)));

        // 第9行
        sheet1.createRow(8);
        sheet1.addMergedRegion(new CellRangeAddress(8, 8, 0, 8));

        // 第10行
        setRowContent(sheet1, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                contentStyle, titleStyle2, contentStyle, contentStyle, contentStyle),
                null, 9, Lists.newArrayList("理论课：", "理论视频总时长：",
                        basicData.get(13) == null || Objects.equals(basicData.get(13), "null") ?
                                "" : basicData.get(13), "理论学习总时长：",
                        basicData.get(14) == null || Objects.equals(basicData.get(14), "null") ?
                                "" : basicData.get(14),
                        "随堂练习提交率：",
                        basicData.get(15) == null || Objects.equals(basicData.get(15), "null") ?
                                "" : basicData.get(15), "", ""));
        sheet1.addMergedRegion(new CellRangeAddress(9, 9, 6, 8));

        // 第11行
        sheet1.createRow(10);
        sheet1.addMergedRegion(new CellRangeAddress(10, 10, 0, 8));

        // 第12行
        setRowContent(sheet1, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                contentStyle, titleStyle2, contentStyle, titleStyle2, contentStyle),
                null, 11, Lists.newArrayList("随堂表现：", "问答回答总次数：",
                        basicData.get(16) == null || Objects.equals(basicData.get(16), "null") ?
                                "" : basicData.get(16), "问答回答表现：",
                        basicData.get(17) == null || Objects.equals(basicData.get(17), "null") ?
                                "" : basicData.get(17), "判断回答总次数：",
                        basicData.get(18) == null || Objects.equals(basicData.get(18), "null") ?
                                "" : basicData.get(18),
                        "判断回答表现：",
                        basicData.get(19) == null || Objects.equals(basicData.get(19), "null") ?
                                "" : basicData.get(19)));

        // 第13行
        sheet1.createRow(12);
        sheet1.addMergedRegion(new CellRangeAddress(12, 12, 0, 8));

        // 第14行
        setRowContent(sheet1, Lists.newArrayList(titleStyle3, titleStyle2, contentStyle, titleStyle2,
                contentStyle, contentStyle, contentStyle, contentStyle, contentStyle),
                null, 13, Lists.newArrayList("问题交流：", "提问问题总数：",
                        basicData.get(20) == null || Objects.equals(basicData.get(20), "null") ?
                                "" : basicData.get(20), "班级回答问题总数：",
                        basicData.get(21) == null || Objects.equals(basicData.get(21), "null") ?
                                "" : basicData.get(21), "", "", "", ""));
        sheet1.addMergedRegion(new CellRangeAddress(13, 13, 4, 8));

        // 设置边框
        setBorder(sheet1, 0, 13, 0, 8);

        HSSFSheet sheet2 = workbook.createSheet("学生详情");
        int examNum = listData.get(0).size() - 22;
        // 设置列宽
        sheet2.setColumnWidth(0, 256 * 10);
        sheet2.setColumnWidth(1, 256 * 14);
        sheet2.setColumnWidth(2, 256 * 15);
        sheet2.setColumnWidth(3, 256 * 16);
        sheet2.setColumnWidth(4, 256 * 16);
        sheet2.setColumnWidth(5, 256 * 12);
        sheet2.setColumnWidth(6, 256 * 16);
        sheet2.setColumnWidth(7, 256 * 14);
        sheet2.setColumnWidth(8, 256 * 17);
        sheet2.setColumnWidth(9, 256 * 17);
        sheet2.setColumnWidth(10, 256 * 15);
        sheet2.setColumnWidth(11, 256 * 16);
        sheet2.setColumnWidth(12, 256 * 25);
        sheet2.setColumnWidth(13, 256 * 11);
        sheet2.setColumnWidth(14, 256 * 11);
        sheet2.setColumnWidth(15, 256 * 11);
        sheet2.setColumnWidth(16, 256 * 25);
        sheet2.setColumnWidth(17, 256 * 11);
        sheet2.setColumnWidth(18, 256 * 11);
        sheet2.setColumnWidth(19, 256 * 13);
        sheet2.setColumnWidth(20, 256 * 11);
        sheet2.setColumnWidth(21, 256 * 11);
        if (examNum > 0) {
            for (int j = 0; j < examNum; j++) {
                sheet2.setColumnWidth(22 + j, 256 * 10);
            }
        }

        // 第一行
        HSSFRow row21 = sheet2.createRow(0);
        row21.setHeight((short) 600);
        HSSFCell cell21 = row21.createCell(0);
        cell21.setCellValue(String.format("班级：%s    课程：%s    学生学习情况详情",
                basicData.get(1) == null || Objects.equals(basicData.get(1), "null") ? "" : basicData.get(1),
                basicData.get(0) == null || Objects.equals(basicData.get(0), "null") ? "" : basicData.get(0)));
        cell21.setCellStyle(titleStyle);
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 21 + examNum));

        // 第二行及列表数据
        setTableData(sheet2, titleStyle2, titleData, 1, titleStyle4);
        for (int index = 0; index < listData.size(); index++) {
            List<String> data = listData.get(index);
            List<CellStyle> styles = Lists.newArrayList();
            for (int y = 0; y < data.size(); y++) {
                styles.add(contentStyle);
            }
            setRowContent(sheet2, styles, (short) 300, 3 + index, data);
        }

        return workbook;

    }

    private static void setRowContent(HSSFSheet sheet, List<CellStyle> styles, Short rowHeight,
                                      Integer rowNum, List<String> cellContents) {
        HSSFRow row = sheet.createRow(rowNum);
        row.setHeight(rowHeight == null ? (short) 380 : rowHeight);
        for (int i = 0; i < cellContents.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(cellContents.get(i) == null || Objects.equals(cellContents.get(i), "null") ?
                    "" : cellContents.get(i));
            CellStyle style = styles.get(i);
            if (style != null) {
                cell.setCellStyle(style);
            }

        }
    }

    public static void setCellBorder(CellStyle style) {
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setWrapText(true);
    }

    public static Font setFont(HSSFWorkbook workbook, Short fontSize, boolean isBold, IndexedColors colorEnum) {
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints(fontSize == null ? (short) 11 : fontSize);
        font.setColor(colorEnum == null ? IndexedColors.BLACK.getIndex() : colorEnum.getIndex());
        font.setBold(isBold);
        return font;
    }

    private static void setCellListData(HSSFSheet sheet, CellStyle style, Integer rowNum, List<String> cellContents) {
        HSSFRow row = sheet.createRow(rowNum);
        for (int i = 0; i < cellContents.size(); i++) {
            HSSFCell cell = row.createCell(i >= 2 ? i + 1 : i);
            if (i == 1) {
                HSSFCell cell2 = row.createCell(2);
                cell2.setCellValue("");
                cell2.setCellStyle(style);
                sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, 2));
            }
            cell.setCellValue(cellContents.get(i) == null || Objects.equals(cellContents.get(i), "null") ?
                    "" : cellContents.get(i));
            cell.setCellStyle(style);
        }
    }

    /**
     * 设置table数据，值相同的进行合并
     *
     * @param sheet
     * @param style
     * @param tableData
     */
    private static void setTableData(HSSFSheet sheet, CellStyle style, Table<Integer, Integer, List<Object>> tableData,
                                     Integer rowNum, CellStyle mergeStyle) {

        Map<String, List<Map<Integer, Integer>>> result = Maps.newHashMap();
        tableData.rowMap().forEach((rowNumber, map) -> {
            Integer num = rowNumber + rowNum;
            HSSFRow row = sheet.createRow(num);
            row.setHeight((short) 300);

            map.forEach((column, values) -> {
                String value = values.get(0).toString();
                HSSFCell cell = row.createCell(column);
                cell.setCellValue(value);
                cell.setCellStyle(style);

                if (values.size() > 1 && Objects.equals(values.get(1), true)) {
                    List<Map<Integer, Integer>> maps = result.get(value);
                    if (CollectionUtils.isEmpty(maps)) {
                        maps = Lists.newArrayList();
                    }
                    Map<Integer, Integer> rowColumnMap = Maps.newHashMap();
                    rowColumnMap.put(num, column);
                    maps.add(rowColumnMap);
                    result.put(value, maps);
                }
            });
        });

        result.forEach((s, list) -> {
            if (list.size() > 1) {
                Integer firstRow = null;
                Integer lastRow = null;
                Integer firstColumn = null;
                Integer lastColumn = null;
                for (Map<Integer, Integer> keyValue : list) {
                    Integer rowNumber = Lists.newArrayList(keyValue.keySet()).get(0);
                    Integer columnNum = Lists.newArrayList(keyValue.values()).get(0);

                    if (firstRow == null && lastRow == null && firstColumn == null && lastColumn == null) {
                        firstRow = rowNumber;
                        lastRow = rowNumber;
                        firstColumn = columnNum;
                        lastColumn = columnNum;
                    } else if (firstRow >= rowNumber && firstColumn >= columnNum) {
                        firstRow = rowNumber;
                        firstColumn = columnNum;
                    } else if (lastRow <= rowNumber && lastColumn <= columnNum) {
                        lastRow = rowNumber;
                        lastColumn = columnNum;
                    }

                }
                sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
                if (firstRow == lastRow) {
                    // 设置行项合并进行居中
                    HSSFCell cell = sheet.getRow(firstRow).getCell(firstColumn);
                    cell.setCellStyle(mergeStyle);
                }
            }
        });
    }

    private static void setBorder(HSSFSheet sheet, Integer firstRow, Integer lastRow,
                                  Integer firstCol, Integer lastCol) {
        CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        RegionUtil.setBorderBottom(BorderStyle.MEDIUM, cra, sheet);
        RegionUtil.setBorderLeft(BorderStyle.MEDIUM, cra, sheet);
        RegionUtil.setBorderRight(BorderStyle.MEDIUM, cra, sheet);
        RegionUtil.setBorderTop(BorderStyle.MEDIUM, cra, sheet);
    }

    public static void ExcelOutputStream(String fileName, HttpServletRequest request,
                                         HttpServletResponse response, ByteArrayOutputStream os) {
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((fileName).getBytes(), "ISO-8859-1") + ".xls");
        } catch (UnsupportedEncodingException e) {
            LOGGER.error(e.getMessage(), e);
        }
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;

        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        } finally {
            if (bis != null)
                try {
                    bis.close();
                } catch (IOException e) {
                    LOGGER.error(e.getMessage(), e);
                }
            if (bos != null)
                try {
                    bos.close();
                } catch (IOException e) {
                    LOGGER.error(e.getMessage(), e);
                }
        }
    }

}
